﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
using Framework;

namespace Enterprise
{
    public class AlbumService : BaseService
    {
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            return DbHelperOleDb.Query("select * from TbAlbum where CoAlbumId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns>Table 返回一张表格</returns>
        public Table GetTable(int pageValue = 0, int rowsValue = 0, string sort = "CoAlbumId", string order = "Desc", string name = "", string keywork = "")
        {
            var strWhere = new StringBuilder();
            strWhere.Append(" where (coname like '%'+@CoName+'%' or conameen like '%'+@CoName+'%') ");
            strWhere.Append(" And (CoKeyWord like '%'+@CoKeyWord+'%' or CoKeyWorden like '%'+@CoKeyWord+'%') ");
            var sbSql = new StringBuilder();
            sbSql.AppendFormat("select top {0} * from TbAlbum {1}", rowsValue, strWhere);
            if (pageValue > 1)
            {
                sbSql.AppendFormat("CoAlbumId not in(select top {0} CoAlbumId from TbAlbum {1} order by {2} {3},CoAlbumId desc)", rowsValue * (pageValue - 1), strWhere, sort, order);
            }
            sbSql.AppendFormat(" order by {0} {1},CoAlbumId desc", sort, order);
            var parameters = new[]
                {
                    new OleDbParameter("@CoName", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoKeyWord", OleDbType.VarChar, 255)
                };
            parameters[0].Value = name;
            parameters[1].Value = name;
            var ds = DbHelperOleDb.Query(sbSql.ToString()).Tables["ds"];
            var table = new Table
            {
                Rows = ds.Rows,
                DataTable = ds,
                Total = DbHelperOleDb.GetSingle("select count(*) from TbAlbum {0}".FormatWith(strWhere)).SafeToInt()
            };
            return table;
        }
        public int Add(
            string name = "",
            string nameEn = "",
            string descirption = "",
            string descirptionEn = "",
            string keyword = "",
            string keywordEn = "",
            string content = "",
            string contentEn = "",
            int sort = 0,
            int type = 0,
            bool isNew = false,
            DateTime? date = null
            )
        {
            var sbSql = new StringBuilder();
            sbSql.Append("insert into tbablum(");
            sbSql.Append("CoName,CoNameEn,CoTypeName,CoTypeNameEn,CoDescirption,CoDescirptionEn,CoKeyWord,CoKeyWordEn,CoContent,CoContentEn,CoType,CoDate,CoSort,CoIsNew");
            sbSql.Append(") values(");
            sbSql.Append("@CoName,@CoNameEn,@CoTypeName,@CoTypeNameEn,@CoDescirption,@CoDescirptionEn,@CoKeyWord,@CoKeyWordEn,@CoContent,@CoContentEn,@CoType,@CoDate,@CoSort,@CoIsNew");
            sbSql.Append(")");
            var parameters = new[]
                {
                    new OleDbParameter("@CoName", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoNameEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoTypeName", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoTypeNameEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescirption", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescirptionEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoContent", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoContentEn", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoType", OleDbType.Integer),
                    new OleDbParameter("@CoDate", OleDbType.Date),
                    new OleDbParameter("@CoSort", OleDbType.Integer),
                    new OleDbParameter("@CoIsNew", OleDbType.Boolean)
                };
            parameters[0].Value = name;
            parameters[1].Value = nameEn;
            parameters[2].Value = "";
            parameters[3].Value = "";
            parameters[4].Value = descirption;
            parameters[5].Value = descirptionEn;
            parameters[6].Value = content;
            parameters[7].Value = contentEn;
            parameters[8].Value = type;
            parameters[9].Value = date;
            parameters[10].Value = sort;
            parameters[11].Value = isNew;
            var newId = DbHelperOleDb.Add(sbSql.ToString(), parameters);
            return newId;
        }



    }
}
